package com.roc.dao.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.NonUniqueResultException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import com.roc.bean.TermBean;
import com.roc.dao.DaoException;
import com.roc.dao.ITerminalDao;
import com.roc.pojo.Estidinfo;
import com.roc.pojo.Operators;
import com.roc.pojo.TPosInfo;
import com.roc.pojo.TPosReplace;
import com.roc.pojo.TblDictionaryItem;
import com.roc.pojo.TblDictionaryLayerItem;
import com.roc.pojo.TblTermInf;
import com.roc.pojo.TermInfo;
import com.roc.pojo.TermInfoTmp;
import com.roc.pojo.Termapply;
import com.roc.pojo.YjPosAgent;
import com.roc.pojo.YjPosInfo;
import com.roc.pojo.YjSnInfo;
import com.roc.util.Constance;
import com.roc.util.constant.Constant;
import com.roc.util.constant.Role;
import com.roc.util.constant.TerminalFlag;

/**
 * 
 * @author 陈锴
 * 
 * 2014-08-13
 * 
 * 终端管理
 *
 */

@Repository
public class ITerminalDaoImpl extends BaseDaoImpl implements ITerminalDao {

	@Override
	public Termapply queryTermapplyId(long id) throws DaoException {
		Termapply result = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			result = (Termapply) session.get(Termapply.class, id);
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询终端申请对象出错");
		}
		return result;
	}

	@Override
	public int queryMaxTermapply() throws DaoException {
		int maxTid=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("select tid from TermTid");
			
			maxTid = (Integer)query.uniqueResult();
		}catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(), e);
			throw new DaoException("查询最大终端号出错");
		}
		return maxTid;
	}

	@Override
	public void editTermTid(int tid) throws DaoException {
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("update TermTid set tid = "+tid);
			query.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(), e);
			throw new DaoException("修改终端号表终端号出错");
		}
	}

	@Override
	public YjPosInfo queryYjPosInfo(String serialNum,String posBrand) throws DaoException {
		YjPosInfo result = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("from YjPosInfo y where y.serialNum = :serialNum and y.posBrand = :posBrand");
			query.setString("serialNum", serialNum);
			query.setString("posBrand", posBrand);
			result = (YjPosInfo) query.uniqueResult();
		}catch(NonUniqueResultException nure){
			logger.debug("查询YjPosInfo不唯一", nure);
			throw new DaoException("查询终端流水号不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询终端流水号不唯一,查询出错");
		}
		return result;
	}

	@Override
	public TPosInfo queryYsbYjPosInfo(String serialNum,String posBrand) throws DaoException {
		TPosInfo result = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("from TPosInfo y where y.serialNum = :serialNum and y.posBrand = :posBrand");
			query.setString("serialNum", serialNum);
			query.setString("posBrand", posBrand);
			result = (TPosInfo) query.uniqueResult();
		}catch(NonUniqueResultException nure){
			logger.debug("查询TPosInfo不唯一", nure);
			throw new DaoException("查询云商宝终端流水号不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询云商宝终端流水号不唯一,查询出错");
		}
		return result;
	}
	@Override
	public TermInfo querTermInfo(String tid) throws DaoException {
		TermInfo result = null;
		
		   try {
			Session session=sessionFactory.getCurrentSession();
			   Query query=session.createQuery("from TermInfo t where t.termId = :termId");
			   query.setString("termId", tid);
			   result=(TermInfo) query.uniqueResult();
		   }catch(NonUniqueResultException nure){
				logger.debug("查询TermInfo不唯一", nure);
				throw new DaoException("查询终端对象不唯一");
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("查询终端关联表出错");
			}
		
		   return result;
	}
	//根据商户号查询终端
	@Override
	public List<TermBean> queryTerByMid(String mid) throws DaoException {
		String  sql="SELECT e.TID_KIND,t.TERM_ADDR,t.TERMINAL_NAME,t.SERIAL_NUM,e.NOTE,t.TERMID FROM ESTIDINFO e ,t_term_info t"
				+" WHERE e.tid=t.TERMID AND e.mid=? ";
			Session session=sessionFactory.getCurrentSession();
		   Query query=session.createSQLQuery(sql);
		   query.setString(0, mid);
		 	List<TermBean> terList=new ArrayList<TermBean>();
		 	Iterator iterator= query.list().iterator();
		 	while(iterator.hasNext()){
		 		Object[] obj = (Object[]) iterator.next();
		 		TermBean  tb=new TermBean();
		 		tb.setTidKind(Constance.Null2EmptyTrim(obj[0]));
		 		tb.setTermAddress(Constance.Null2EmptyTrim(obj[1]));
		 		tb.setTermName(Constance.Null2EmptyTrim(obj[2]));
		 		tb.setSerialNum(Constance.Null2EmptyTrim(obj[3]));
		 		tb.setTermNote(Constance.Null2EmptyTrim(obj[4]));
		 		tb.setTid(Constance.Null2EmptyTrim(obj[5]));
		 		
		 		terList.add(tb);
		 	}
		return terList;
	}

	@Override
	public Estidinfo queryEstidinfo(String tid) throws DaoException {
		Estidinfo result = null;
		
		   try {
			Session session=sessionFactory.getCurrentSession();
			   Query query=session.createQuery("from Estidinfo t where t.tid = :termId");
			   query.setString("termId", tid);
			   result=(Estidinfo) query.uniqueResult();
		   }catch(NonUniqueResultException nure){
				logger.debug("查询Estidinfo不唯一", nure);
				throw new DaoException("查询终端对象不唯一");
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("查询终端表出错");
			}
		
		   return result;
	}
	@Override
	public List<Object> getYsbTermapplyList(String isused,String seriaNum, Long agentid,String posBrand, String posType, String posName,String roleType) {
		String sql = "select a.SERIAL_NUM, a.POS_BRAND, a.POS_TYPE, a.POS_NAME, b.IS_USED  from t_pos_info a, t_pos_agent b where a.SERIAL_NUM = b.SERIAL_NUM and a.POS_BRAND = b.POS_BRAND  and a.TEK_ID ='Y' ";
		if(isused!=null &&!"".equals(isused)){
			sql+=" and b.IS_USED = '"+isused+"'";
		}
		if(!"14".equals(roleType)){
			sql+=" and b.zs_flag ='Y'";
		}
		if(seriaNum!=null &&!"".equals(seriaNum)){
			sql+=" and b.SERIAL_NUM ='"+seriaNum+"'";
		}
	    if(agentid!=null){
			sql+=" and b.agent_Id ="+agentid;
		}
	    if(posBrand!=null &&!"".equals(posBrand)){
			sql+=" and a.pos_Brand ='"+posBrand+"'";

	    }
	    if(posType!=null &&!"".equals(posType)){
			sql+=" and a.pos_Type ='"+posType+"'";
	    }
	    if(posName!=null &&!"".equals(posName)){
			sql+=" and a.pos_Name ='"+posName+"'";
	    }
		Session session=sessionFactory.getCurrentSession();
		Query query=session.createSQLQuery(sql);
		return query.list();
	}
	

	@Override
	public List<Object> getTermapplyList(String isused,String seriaNum, Long agentid,String posBrand, String posType, String posName) {
		String sql = "select a.SERIAL_NUM, a.POS_BRAND, a.POS_TYPE, a.POS_NAME, b.IS_USED  from yj_pos_info a, yj_pos_agent b where a.SERIAL_NUM = b.SERIAL_NUM and a.POS_BRAND = b.POS_BRAND and b.IS_USED ='N' and a.TEK_ID ='Y' ";
		/*if(isused!=null &&!"".equals(isused)){
			sql+=" and b.IS_USED = '"+isused+"'";
		}*/
		if(seriaNum!=null &&!"".equals(seriaNum)){
			sql+=" and b.SERIAL_NUM ='"+seriaNum+"'";
		}
	    if(agentid!=null){
			sql+=" and b.agent_Id ="+agentid;
		}
	    if(posBrand!=null &&!"".equals(posBrand)){
			sql+=" and a.pos_Brand ='"+posBrand+"'";

	    }
	    if(posType!=null &&!"".equals(posType)){
			sql+=" and a.pos_Type ='"+posType+"'";
	    }
	    if(posName!=null &&!"".equals(posName)){
			sql+=" and a.pos_Name ='"+posName+"'";
	    }
		Session session=sessionFactory.getCurrentSession();
		Query query=session.createSQLQuery(sql);
		return query.list();
	}
	
    /**
     * 查询yj_pos_info表记录数
     */
	@Override
	public long getPosInfo(String seriaNum, String posBrand, String posType,
			String posName) {
		BigDecimal value = null;
		String sql ="select COUNT(*) from yj_pos_info p where 1=1 ";
		   if(seriaNum!=null &&!"".equals(seriaNum)){
			sql+=" and p.SERIAL_NUM ='"+seriaNum+"'";
		   }
		   if(posBrand!=null &&!"".equals(posBrand)){
				sql+=" and p.pos_Brand ='"+posBrand+"'";
		    }
		    if(posType!=null &&!"".equals(posType)){
				sql+=" and p.pos_Type ='"+posType+"'";
		    }
		    if(posName!=null &&!"".equals(posName)){
				sql+=" and p.pos_Name ='"+posName+"'";
		    }
		    Session session=sessionFactory.getCurrentSession();
			Query query=session.createSQLQuery(sql);
			value = (BigDecimal)query.uniqueResult();
		    return value.longValue();
	}
	/**
     * 查询t_pos_info表记录数
     */
	@Override
	public long getTPosInfo(String seriaNum, String posBrand, String posType,
			String posName) {
		BigDecimal value = null;
		String sql ="select COUNT(*) from t_pos_info p where 1=1 ";
		   if(seriaNum!=null &&!"".equals(seriaNum)){
			sql+=" and p.SERIAL_NUM ='"+seriaNum+"'";
		   }
		   if(posBrand!=null &&!"".equals(posBrand)){
				sql+=" and p.pos_Brand ='"+posBrand+"'";
		    }
		    if(posType!=null &&!"".equals(posType)){
				sql+=" and p.pos_Type ='"+posType+"'";
		    }
		    if(posName!=null &&!"".equals(posName)){
				sql+=" and p.pos_Name ='"+posName+"'";
		    }
		    Session session=sessionFactory.getCurrentSession();
			Query query=session.createSQLQuery(sql);
			value = (BigDecimal)query.uniqueResult();
		    return value.longValue();
	}

	@Override
	public YjPosAgent getYjPosAgent(String serialNum, Long agentId)
			throws DaoException {
		YjPosAgent result = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("from YjPosAgent y where y.serialNum = :serialNum and y.agentId = :agentId");
			query.setString("serialNum", serialNum);
			query.setLong("agentId", agentId);
			result = (YjPosAgent) query.uniqueResult();
		}catch(NonUniqueResultException nure){
			logger.debug("查询YjPosAgent不唯一", nure);
			throw new DaoException("查询终端流水号不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询终端流水号不唯一,查询出错");
		}
		return result;
	}

	@Override
	public List getOpertorsIdsTreeById(Long id) throws DaoException{
		String sql = "SELECT id FROM t_operators START WITH ";
	    if(id!=null){
			sql+="ID="+id+" CONNECT BY supri_id = PRIOR  id";
		}else{
			throw new DaoException("查询ID参数不能为空");
		}
		Session session=sessionFactory.getCurrentSession();
		Query query=session.createSQLQuery(sql);
		return query.list();
	}

	@Override
	public void deletePosAgentByAgentIdsAndSN(String sn, List<Long> list)
			throws DaoException {
		// TODO Auto-generated method stub
		int i = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("delete from YjPosAgent y where y.serialNum = :serialNum and y.agentId in (:agentIds)");
			query.setString("serialNum", sn);
			query.setParameterList("agentIds", list);
			i = query.executeUpdate();
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("删除POSAGENT代理数据失败");
		}
	}

	@Override
	public void updatePosAgentByAgentAndSN(String sn, Long agentId) throws DaoException{
		int i = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("update YjPosAgent y set y.isUsed=:isUsed,y.dtLostmod=sysdate where y.serialNum = :serialNum and y.agentId =:agentId");
			query.setString("isUsed", "N");
			query.setString("serialNum", sn);
			query.setLong("agentId", agentId);
			i = query.executeUpdate();
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("删除POSAGENT代理数据失败");
		}
	}

	@Override
	public List getPosInfosForBatchRecall(String seriaNum,String orgCode,Long agentId, String posBrand,
			String posType, String posName,String isUsed) throws DaoException {
		String sql = "select a.SERIAL_NUM  from yj_pos_info a, yj_pos_agent b where a.SERIAL_NUM = b.SERIAL_NUM and not exists (select 1 from t_term_info t where t.serial_num = a.serial_num) ";
		if(orgCode!=null &&!"".equals(orgCode)){
			sql+=" and a.ORG_CODE ='"+orgCode+"'";
		}
		if(seriaNum!=null &&!"".equals(seriaNum)){
			sql+=" and b.SERIAL_NUM ='"+seriaNum+"'";
		}
	    if(agentId!=null){
			sql+=" and b.agent_Id ="+agentId;
		}
	    if(posBrand!=null &&!"".equals(posBrand)){
			sql+=" and a.pos_Brand ='"+posBrand+"'";

	    }
	    if(posType!=null &&!"".equals(posType)){
			sql+=" and a.pos_Type ='"+posType+"'";
	    }
	    if(posName!=null &&!"".equals(posName)){
			sql+=" and a.pos_Name ='"+posName+"'";
	    }
	    if(isUsed!=null &&!"".equals(isUsed)){
			sql+=" and b.is_used ='"+isUsed+"'";
	    }
		Session session=sessionFactory.getCurrentSession();
		Query query=session.createSQLQuery(sql);
		return query.list();
	}

	@Override
	public void deletePosAgentByAgentIdAndSn(Long agentId, String sn)
			throws DaoException {
		int i = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql = "delete from yj_pos_agent p where 1=1 ";
			if(agentId!=null ){
				sql+= " and p.agent_id='"+agentId+"'";
				
			}
			if(sn!=null && !"".equals(sn)){
				sql+=" and p.SERIAL_NUM ='"+sn+"'";
			}
			Query query = session.createSQLQuery(sql);
			i = query.executeUpdate();	
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("删除代理商分配规则出错");
		}
	}

	@Override
	public void deletePosInfo(String sn, String posBrand, String posType,
			String posName) throws DaoException {
		int i = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql = "delete from yj_pos_info p where 1=1 ";
			if(sn!=null && !"".equals(sn)){
				sql+="  and p.SERIAL_NUM ='"+sn+"'";
			}
			if(posBrand!=null && !"".equals(posBrand)){
				sql+= " and p.pos_brand='"+posBrand+"'";
				
			}
			if(posType!=null && !"".equals(posType)){
				sql+= " and p.pos_type='"+posType+"'";
				
			}
			if(posBrand!=null && !"".equals(posName)){
				sql+= " and p.pos_name='"+posName+"'";
				
			}
			Query query = session.createSQLQuery(sql);
			i = query.executeUpdate();	
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("删除代理商分配规则出错");
		}
	}

	@Override
	public List<TermBean> queryTerChangeByMid(String mid) throws DaoException {
		// TODO Auto-generated method stub
		String  sql="SELECT e.TID_KIND,t.TERM_ADDR,t.TERMINAL_NAME,t.SERIAL_NUM,e.NOTE,t.TERMID FROM ESTIDINFO e ,t_term_info t"
				+" WHERE e.tid=t.TERMID AND e.flag='3' AND e.mid=? ";
			Session session=sessionFactory.getCurrentSession();
		   Query query=session.createSQLQuery(sql);
		   query.setString(0, mid);
		 	List<TermBean> terList=new ArrayList<TermBean>();
		 	Iterator iterator= query.list().iterator();
		 	while(iterator.hasNext()){
		 		Object[] obj = (Object[]) iterator.next();
		 		TermBean  tb=new TermBean();
		 		tb.setTidKind(obj[0].toString());
		 		tb.setTermAddress(obj[1].toString());
		 		tb.setTermName(obj[2].toString());
		 		tb.setSerialNum(obj[3].toString());
		 		tb.setTermNote(Constance.Null2EmptyTrim(obj[4]));
		 		tb.setTid(Constance.Null2EmptyTrim(obj[5]));
		 		
		 		terList.add(tb);
		 	}
		return terList;
	}
	@Override
	public List<TermInfoTmp> queryTermInfoTmpByMid(String mid)
			throws DaoException {
		// TODO Auto-generated method stub
		List<TermInfoTmp> tList=null;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql = "from TermInfoTmp t where t.serialNum is not null and t.mid=:mid ";
			Query query = session.createQuery(sql);
			query.setString("mid",mid);
		    tList=query.list();
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询终端临时信息表出错");
		}
		return tList;
	}

	@Override
	public List<TermInfoTmp> queryTermTmpNULLByMid(String mid)
			throws DaoException {
		// TODO Auto-generated method stub
		List<TermInfoTmp> tList=null;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql = "from TermInfoTmp t where t.serialNum is null and t.mid=:mid ";
			Query query = session.createQuery(sql);
			query.setString("mid",mid);
		    tList=query.list();
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询终端临时信息表出错");
		}
		return tList;
	}

	@Override
	public List<TblDictionaryLayerItem> queryModelItemByType(
			TblDictionaryLayerItem item) throws DaoException {
		List<TblDictionaryLayerItem> tList=new ArrayList<TblDictionaryLayerItem>();
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql = "from TblDictionaryLayerItem t "
						+ "where dic_type=:dicType AND DIC_PCODE =:dicPcode ";

			Map<String, String> params = new HashMap<String, String>();
			params.put("dicType",item.getId().getDicType());
			params.put("dicPcode",item.getId().getDicPcode());
			if(null != item.getId().getDicCode() && !"".equals(item.getId().getDicCode())){
				sql = sql +  " AND DIC_CODE = :dicCode ";
				params.put("dicCode",item.getId().getDicCode());
			}
			sql = sql + "order by dic_code";
			Query query = session.createQuery(sql);
			
			for (String key : params.keySet()) {
				query.setString(key,params.get(key));
			}
			
		    tList=query.list();
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询型号信息表出错");
		}
		return tList;
	}

	@Override
	public List<TblDictionaryItem> queryDictionaryItemByType(
			TblDictionaryItem item , String symbol) throws DaoException {
		List<TblDictionaryItem> tList=new ArrayList<TblDictionaryItem>();
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql = "from TblDictionaryItem t "
						+ " where DIC_TYPE " + symbol + " :dicType ";

			Map<String, String> params = new HashMap<String, String>();
			params.put("dicType",item.getId().getDicType());
			if(null != item.getId().getDicCode() && !"".equals(item.getId().getDicCode())){
				sql = sql +  " and DIC_CODE = :dicCode ";
				params.put("dicCode",item.getId().getDicCode());
			}
			sql = sql + "order by to_number(dic_code)";
			Query query = session.createQuery(sql);
			
			for (String key : params.keySet()) {
				query.setString(key,params.get(key));
			}
			
			tList=query.list();
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询基础信息表出错");
		}
		return tList;
	}

	@Override
	public void setSerialNumber(String termID,String merchtNo,YjPosInfo yjPos,String orgCode)throws DaoException {
	
		try {
		Session session = sessionFactory.getCurrentSession();
		String sql="update tbl_term_inf tt set tt.DEVICE_CD=:serial_num,tt.belong_sub=:belong_sub,tt.term_tp=:term_tp,tt.TMNL_BRAND=:brand,tt.TMNL_MODEL_NO=:model_no,tt.REC_UPD_TS=systimestamp,tt.REC_OPR_ID='U',tt.status=:status where tt.TERM_ID=:termid and tt.MCHT_CD=:merchtNo";
		SQLQuery query = session.createSQLQuery(sql);
		query.setParameter("serial_num", yjPos.getSerialNum());
		query.setParameter("belong_sub", orgCode);
		query.setParameter("term_tp",yjPos.getPosType());
		query.setParameter("brand",yjPos.getPosBrand());
		query.setParameter("model_no",yjPos.getPosName());
		query.setParameter("status", TerminalFlag.FLAG_ONE.getCode());
		query.setParameter("termid", termID);
		query.setParameter("merchtNo", merchtNo);
		int count=query.executeUpdate();
		if(count>0)
		{
			logger.info("绑定成功!");
		}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("分配SN序列号出错");
		}
	}

	@Override
	public void updateSerialNumberStatus(String serialNum,String posBrandCode,long agentid,String accountNo,String tid) throws DaoException {
		int count=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="update yj_pos_agent ya set ya.is_used='Y',ya.dt_lastmod=systimestamp,ya.remark=:accountNo where ya.serial_num=:serialNum and ya.agent_id=:agentid and ya.POS_BRAND = :posBrand ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("serialNum", serialNum);
			query.setParameter("agentid", agentid);
			query.setParameter("accountNo", "代理商【"+accountNo+"】分配终端【"+tid+"】的SN序列号为【"+serialNum + "-" + posBrandCode + "】");
			query.setParameter("posBrand", posBrandCode);
			count=query.executeUpdate();
			if(count>0)
			{
				logger.info("更新yj_pos_agent状态成功!");
			}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("更新yj_pos_agent出错");
			}
	}

	@Override
	public void updateSerialNumberStatus2(String serialNum,String posBrandCode,String mid,String tid) throws DaoException {
		try {
			int count=0;
			Session session = sessionFactory.getCurrentSession();
			String sql="update yj_pos_info yi set yi.is_bind='Y',yi.dt_lastmod=systimestamp,yi.merc_code=:mid,yi.term_code=:tid where yi.serial_num=:serialNum and yi.POS_BRAND = :posBrand";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("mid", mid);
			query.setParameter("tid", tid);
			query.setParameter("serialNum", serialNum);
			query.setParameter("posBrand", posBrandCode);
			count=query.executeUpdate();
			if(count>0)
			{
				logger.info("更新yj_pos_info状态成功!");
			}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("更新yj_pos_info出错");
			}
	}

	@Override
	public TblTermInf queryTermInfo(String tid,String mid) throws DaoException {
		TblTermInf result = null;
		
		   try {
			Session session=sessionFactory.getCurrentSession();
			Query query=session.createQuery("from TblTermInf t where t.id.TermId = :termId AND t.id.MchtCd=:mid");   
				query.setString("termId", tid);
				query.setString("mid", mid);
			   result=(TblTermInf) query.uniqueResult();
		   }catch(NonUniqueResultException nure){
				logger.debug("查询TblTermInf不唯一", nure);
				throw new DaoException("查询终端对象不唯一");
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("查询终端表出错");
			}
		
		   return result;
	}

	@Override
	public void delTermSNInfo(String sn,String posBandCode, Operators op)
			throws DaoException {
		int i = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql = "delete from yj_pos_info p where p.IS_BIND = 'N' and p.OPERATORS = '" + op.getAccountNo() + "'";
			
			sql += " and p.SERIAL_NUM = '" + sn + "' and p.POS_BRAND = '" + posBandCode + "' ";
			Query query = session.createSQLQuery(sql);
			i = query.executeUpdate();	
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("删除sn信息出错");
		}
	}

	@Override
	public void delTermSNAgentInfo(String sn,String posBandCode, Operators op)
			throws DaoException {
		int i = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sb = new StringBuffer();
			sb.append( "DELETE FROM YJ_POS_AGENT Y " );
			sb.append( " WHERE Y.SERIAL_NUM IN (SELECT P.SERIAL_NUM " );
			sb.append( " 	FROM YJ_POS_INFO P " );
			sb.append( " 	WHERE P.IS_BIND <> 'Y'  " );
			sb.append( " 		AND P.SERIAL_NUM = '" + sn + "' ");
			sb.append( " 		and p.POS_BRAND = '" + posBandCode + "'  ) " );
			sb.append( " AND Y.POS_BRAND = '" + posBandCode + "' " );

			Query query = session.createSQLQuery(sb.toString());
			i = query.executeUpdate();	
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("删除sn关联信息出错");
		}
	}

	@Override
	public int revTermSNAgentInfo(String sn,String posBandCode, Operators op)
			throws DaoException {
		int i = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sb = new StringBuffer();
			
		//	sb.append(" SELECT * ");
			sb.append(" DELETE ");
			sb.append("   FROM YJ_POS_AGENT T ");
			sb.append("  WHERE T.SERIAL_NUM = '" + sn + "' ");
			sb.append("    AND T.POS_BRAND = '" + posBandCode + "' ");
			// 如果当前登陆账号非系统管理员 ，删除 当前账号下的关联信息
			if ( !op.getRoleType().equals( Role.SYSTEM.getCode() ) && !op.getRoleType().equals( Role.WARE_HOUSE_LEAD.getCode() ) ){

				String opId = String.valueOf(op.getId());
				// 如果当前登陆账号是受理市场运营，删除 该SN号 收单事业部下的所有关联信息
				if ( op.getRoleType().equals( Role.OPERATORS_LEAD.getCode() ) ){
					opId = Constant.SHOUDANSHIYEBU_ID;
				}
				sb.append("    AND ");
				// 如果当前登陆账号是收单事业部，删除 该SN号 收单事业部下的所有关联信息以及 该SN号 受理市场运营的关联信息
				if ( op.getRoleType().equals( Role.AGENT.getCode() ) && Constant.SHOUDANSHIYEBU_ID.equals( String.valueOf(op.getId()) ) ){
					sb.append("    (( ");
				}
				sb.append("        T.AGENT_ID in (SELECT T1.AGENT_ID AGENT_ID ");
				sb.append("              FROM (SELECT t.*, ");
				sb.append("                           (SELECT O.SUPRI_ID ");
				sb.append("                              FROM APSMGM.T_OPERATORS O ");
				sb.append("                             WHERE O.ID = T.AGENT_ID) SUP_ID ");
				sb.append("                      FROM APSMGM.Yj_Pos_Agent t ");
				sb.append("                     where t.serial_num = '" + sn + "' ");
				sb.append("                       and t.pos_brand = '" + posBandCode + "') T1 ");
				sb.append("             START WITH t1.AGENT_ID = '" + opId + "' ");
				sb.append("            CONNECT BY PRIOR T1.AGENT_ID = T1.SUP_ID) ");
				sb.append("    AND T.AGENT_ID != '" + opId + "' ");
				// 如果当前登陆账号是收单事业部，删除 该SN号 收单事业部下的所有关联信息以及 该SN号 受理市场运营的关联信息
				if ( op.getRoleType().equals( Role.AGENT.getCode() ) && Constant.SHOUDANSHIYEBU_ID.equals( String.valueOf(op.getId()) )){
					sb.append("    ) or T.AGENT_ID in (  SELECT o1.id ");
					sb.append(" 					   from apsmgm.t_operators O1 ");
					sb.append("  						 where O1.Role_Type = '" + Role.OPERATORS_LEAD.getCode() + "' ) ) ");
				}
				
			} else {
				// 否则，删除非导入账号信息的其他关联信息
				sb.append("    AND T.AGENT_ID != (SELECT (SELECT ID ");
				sb.append("                                 FROM T_OPERATORS O ");
				sb.append("                                WHERE O.ACCOUNT_NO = T2.OPERATORS) ID ");
				sb.append("                  FROM YJ_POS_INFO T2 ");
				sb.append("                 WHERE T2.SERIAL_NUM = '" + sn + "' ");
				sb.append("                   AND T2.POS_BRAND = '" + posBandCode + "' ) ");
			}

			Query query = session.createSQLQuery(sb.toString());
			i = query.executeUpdate();	
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("撤回sn关联信息出错");
		}
		return i;
	}
	@Override
	public void updTermSNAgentUsedStatus(String sn,String posBandCode, Operators op)
			throws DaoException {
		int i = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sb = new StringBuffer();
			
			sb.append(" UPDATE YJ_POS_AGENT Y ");
			sb.append("    SET Y.IS_USED = 'N', ");
			sb.append("            Y.DT_LASTMOD  = SYSTIMESTAMP ");

			// 如果当前登陆账号非系统管理员 ，修改 备注信息为 当前登陆帐号上级 的备注信息
			if ( !op.getRoleType().equals( Role.SYSTEM.getCode() )  && !op.getRoleType().equals( Role.WARE_HOUSE_LEAD.getCode() )  ){/*
				sb.append("        Y.REMARK  = (SELECT REMARK ");
				sb.append("                       FROM (SELECT T.REMARK ");
				sb.append("                               FROM YJ_POS_INFO T ");
				sb.append("                              WHERE T.SERIAL_NUM = '" + sn + "' ");
				sb.append("                                AND T.POS_BRAND = '" + posBandCode + "' ");
				sb.append("                                AND OPERATORS = '" + op.getAccountNo() + "') ");
				sb.append("                     UNION ALL (SELECT T.REMARK ");
				sb.append("                                 FROM YJ_POS_AGENT T ");
				sb.append("                                WHERE T.SERIAL_NUM = '" + sn + "' ");
				sb.append("                                  AND T.POS_BRAND = '" + posBandCode + "' ");
				sb.append("                                  AND T.REMARK LIKE '%分配给[" + op.getAccountNo() + "]') ");
				sb.append("                     ) ");*/
			} else {/*
				// 否则，修改 备注信息为 导入账号 的备注信息
				sb.append("        Y.REMARK  = ( SELECT T.REMARK ");
				sb.append("                               FROM YJ_POS_INFO T ");
				sb.append("                              WHERE T.SERIAL_NUM = '" + sn + "' ");
				sb.append("                                AND T.POS_BRAND = '" + posBandCode + "' ");
				sb.append("                     ) ");*/
			}
			sb.append("  WHERE Y.SERIAL_NUM = '" + sn + "' ");
			sb.append("    AND Y.POS_BRAND = '" + posBandCode + "' ");

			if ( !op.getRoleType().equals( Role.SYSTEM.getCode() )  ){
				sb.append("    AND Y.AGENT_ID = '" + op.getId() + "' ");
			}
			
			Query query = session.createSQLQuery(sb.toString());
			i = query.executeUpdate();	
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("修改sn关联信息 使用状态 出错");
		}
	}
	
	@Override
	public YjSnInfo getOriginSerialNum(String mid, String tid)
			throws DaoException {
		YjSnInfo yjSnInfo = new YjSnInfo();
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="select s.ori_sn || ',' || s.old_pos_brand from yj_sn_info s where s.merc_code=:merchtNo and s.term_code=:termID";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("merchtNo", mid);
			query.setParameter("termID", tid);
			String value = String.valueOf(query.uniqueResult());
			if(null != value && !"".equals(value)  && !"null".equals(value) ){
				String [] arr = value.split(",");
				String oldBrand = (arr.length > 1 ? arr[1] :"" );

				yjSnInfo.setOriSn(Constance.Null2EmptyTrim(arr[0]));
				yjSnInfo.setOldPosBrand(Constance.Null2EmptyTrim(oldBrand));
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("获取原始SN序列号出错");
		}
		return yjSnInfo;
	}

	@Override
	public int unbindTerminal(String serialNum_origin,String serialNum_new,String mid,String tid,String posBrandCode,String oldPosBrandCode) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="update tbl_term_inf t set t.device_cd=:originSN,t.rec_upd_ts=systimestamp,t.rec_opr_id='U',t.status='02',t.tmnl_brand=:oldPosBrandCode where t.device_cd=:newSN and t.term_id=:termID and t.mcht_cd=:mchtID and t.tmnl_brand = :posBrandCode";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("originSN", serialNum_origin);
			query.setParameter("newSN", serialNum_new);
			query.setParameter("termID", tid);
			query.setParameter("mchtID", mid);
			query.setParameter("posBrandCode", posBrandCode);
			query.setParameter("oldPosBrandCode", oldPosBrandCode);
			icount=query.executeUpdate();
			if(icount>0)
			{
				logger.info("解绑成功!!!");
			}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("分配SN序列号出错");
			}
		return icount;
	}

	@Override
	public int unbindAgentPos(String serialNum, String agentID,String accountNo,String termCode,String posBrandCode)throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="update yj_pos_agent y set y.is_used='N',y.remark=:remark ,y.dt_lastmod=systimestamp where y.serial_num=:serialNum and y.POS_BRAND = :posBrandCode and y.agent_id=:agentID ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("remark", "代理商【"+accountNo+"】对终端【"+termCode + "-" + posBrandCode +"】进行解绑操作");
			query.setParameter("serialNum", serialNum);
			query.setParameter("posBrandCode", posBrandCode);
			query.setParameter("agentID", agentID);
			icount=query.executeUpdate();
			if(icount>0)
			{
				logger.info("解绑终端释放yj_pos_agent成功!!!");
			}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("解绑终端释放yj_pos_agent出错");
			}
		return icount;
	}

	@Override
	public int unbindInfoPos(String serialNum,String posBrandCode)throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="update yj_pos_info y set y.merc_code=:orimid,y.term_code=:oritid,y.is_bind='N' where y.serial_num=:serialNum and y.POS_BRAND = :posBrandCode ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("oritid", Constant.TERM_CODE_DEFAULT);
			query.setParameter("orimid", Constant.MERC_CODE_DEFAULT);
			query.setParameter("serialNum", serialNum);
			query.setParameter("posBrandCode", posBrandCode);
			icount=query.executeUpdate();
			if(icount>0)
			{
				logger.info("解绑终端释放yj_pos_info成功!!!");
			}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("解绑终端释放yj_pos_info出错");
			}
		return icount;
	}

	@Override
	public String agentIDbySerialNum(String serialNum, String posBrandCode)
			throws DaoException {
		String agentId=null;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="select max(a.agent_id) from yj_pos_agent a where a.serial_num=:serialNum and a.pos_brand=:posBrandCode";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("serialNum", serialNum);
			query.setParameter("posBrandCode", posBrandCode);
			Object obj=query.uniqueResult();
				if(obj!=null){
					agentId=String.valueOf(obj);//sn对应所属Id
				}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("解绑终端释放yj_pos_info出错");
			}
		return agentId;
	}

	@Override
	public Map<String, String> findYsbTransPos(TPosInfo tPosInfo) throws DaoException {
		Map<String, String> rsult = new HashMap<String, String>();
		try {
			String  sql="select mcht_cd mchtCd,term_id termId,status wmStatus,flag wmFlag,remark1 wmRemark "
					+ " from apstfr.tbl_term_wm_inf s "
					+ " where s.mcht_cd=? and s.term_id=? ";
				Session session=sessionFactory.getCurrentSession();
			   Query query=session.createSQLQuery(sql);
			   query.setString(0, tPosInfo.getMercCode());
			   query.setString(1, tPosInfo.getTermCode());
			 	List<TermBean> terList=new ArrayList<TermBean>();
			 	Iterator iterator= query.list().iterator();
			 	while(iterator.hasNext()){
			 		Object[] obj = (Object[]) iterator.next();
			 		
			 		rsult.put("mchtCd", obj[0].toString());
			 		rsult.put("termId", obj[1].toString());
			 		rsult.put("wmStatus", obj[2].toString());
			 		rsult.put("wmFlag", obj[3].toString());
			 		rsult.put("wmRemark", obj[4].toString());
			 		
			 	}
		}catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(), e);
			throw new DaoException("查询完美账单信息出错");
		}
		return rsult;
	}

	@Override
	public int selectYsbTransInfo(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();

			String sql =  " select count(*)"
					+ " from (select * from apstfr.tbl_tfr_trn_log1 union all "
			        + " select * from apstfr.tbl_tfr_trn_log2) s "
			        + " where s.trans_dt=to_char(sysdate,'YYYYMMDD') "
					+ " and s.resp_cd='00' "
					+ " and s.trans_st='1000000000' "
					+ " and s.ma_trans_cd in ('1011','1171') "
					+ " and s.mchnt_cd=:mchtCd "
					+ " and s.term_id=:termId ";
			Query query = session.createSQLQuery(sql);
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			query.setParameter("termId", tPosInfo.getTermCode());

			String str= (String)query.uniqueResult().toString();
			if(str !=null){
				icount = Integer.parseInt(str);
			}
		}catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(), e);
			throw new DaoException("查询实时交易信息出错");
		}
		return icount;
	}

	@Override
	public int selectYsbFifteenTransInfo(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql =  " select count(*) "
					+ " from hbat.tbl_tfr_trn_log s "
					+ " where s.resp_cd='00'  "
					+ " and s.trans_st='1000000000' "
					+ " and s.ma_trans_cd in ('1011','1171') "
					+ " and s.mchnt_cd=:mchtCd "
					+ " and s.term_id=:termId ";

			Query query = session.createSQLQuery(sql);
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			query.setParameter("termId", tPosInfo.getTermCode());

			String str= (String)query.uniqueResult().toString();
			if(str !=null){
				icount = Integer.parseInt(str);
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询15日交易信息出错");
		}
		return icount;
	
	}

	@Override
	public int selectYsbHistoryTransInfo(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql = " select count(*) "
					+ " from apstfr.tbl_tfr_his_trn_log s "
					+ " where s.resp_cd='00' "
					+ " and s.trans_st='1000000000' "
					+ " and s.ma_trans_cd in ('1011','1171') "
					+ " and s.mchnt_cd=:mchtCd "
					+ " and s.term_id=:termId ";

			Query query = session.createSQLQuery(sql);
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			query.setParameter("termId", tPosInfo.getTermCode());

			String str= (String)query.uniqueResult().toString();
			if(str !=null){
				icount = Integer.parseInt(str);
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询历史交易信息出错");
		}
		return icount;
	
	}

	@Override
	public String selectYsbMaxAgent(TPosInfo tPosInfo) throws DaoException {
		String agentId="";
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql=" select max(to_char(p.dt_lastmod,'yyyy-MM-dd HH24:mi:ssxff6')) from t_pos_agent p where p.serial_num=:serialNum and p.pos_brand=:posBrandCode ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("serialNum", tPosInfo.getSerialNum());
			query.setParameter("posBrandCode", tPosInfo.getPosBrand());
			Object obj=query.uniqueResult();
			if(obj!=null){
				agentId=String.valueOf(obj);// SN关系表最大Id
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询SN最大更新时间出错");
		}
		return agentId;
	}

	@Override
	public void updateYsbPosAgent(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="update t_pos_agent set is_used=:used,dt_lastmod=systimestamp where serial_num=:serialNum AND Pos_Brand=:brand and ZS_FLAG=:zsFlag ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("used", tPosInfo.getIsUsed());
			query.setParameter("serialNum", tPosInfo.getSerialNum());
			query.setParameter("brand", tPosInfo.getPosBrand());
			query.setParameter("zsFlag", "Y");
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("修改云商宝SN关系信息成功!!!");
			}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("修改云商宝SN关系信息出错");
			}
	}

	@Override
	public void unboundYsbPosInfo(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="update t_pos_info s set s.is_bind='N',s.dt_lastmod=systimestamp,s.merc_code='000000000000000',s.term_code='00000000' where s.serial_num=:serialNum and s.pos_brand=:brand ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("serialNum", tPosInfo.getSerialNum());
			query.setParameter("brand", tPosInfo.getPosBrand());
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("修改云商宝SN信息成功!!!");
			}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("修改云商宝SN信息出错");
			}
	}

	@Override
	public void dealWMRecord(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="delete from apstfr.tbl_term_wm_inf s where s.mcht_cd=:mchtCd and s.term_id=:termId";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			query.setParameter("termId", tPosInfo.getTermCode());
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("删除完美账单的数据成功!!!");
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("删除完美账单的数据出错");
		}
	
	}

	@Override
	public String queryExistSn(TPosInfo tPosInfo) throws DaoException {
		String new_sn="";
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql=" select s.ori_sn from yj_sn_info s where s.merc_code=:mchtCd and s.term_code=:termId ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			query.setParameter("termId", tPosInfo.getTermCode());
			Object obj=query.uniqueResult();
			if(obj!=null){
				new_sn=String.valueOf(obj);// 原始SN号
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询原始SN出错");
		}
		return new_sn;
	}
	

	// 
	@Override
	public int serialNumGenerator(TPosInfo tPosInfo) throws DaoException {
		int YJ_sn = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql=" select seq_device_cd.nextval from dual ";
			SQLQuery query = session.createSQLQuery(sql);
			Object obj=query.uniqueResult();
			if(obj!=null){
				YJ_sn=Integer.valueOf(obj.toString());// Yj***
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询下个原始序列号出错");
		}
		return YJ_sn;
	}

	@Override
	public void unboundTerminal(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql = "update tbl_term_inf t set t.device_cd=:serialNum, "
						+ " t.tmnl_brand=:brand,t.tmnl_model_no=:model,t.rec_upd_ts=systimestamp, " 
						+ " t.rec_opr_id='U',t.status='02' "
						+ "where t.mcht_cd=:mchtCd and t.term_id=:termId ";
			SQLQuery query = session.createSQLQuery(sql);

			query.setParameter("serialNum", tPosInfo.getSerialNum());
			query.setParameter("brand", tPosInfo.getPosBrand());
			query.setParameter("model", tPosInfo.getPosName());
			
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			query.setParameter("termId", tPosInfo.getTermCode());
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("云商宝终端解绑成功!!!");
			}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("云商宝终端解绑出错");
			}
	
	}
	
	// 

	@Override
	public void insertSnInfo(YjSnInfo snInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql = " insert into yj_sn_info values(:mchtCd,:termId,:oriSn,:newSn,null,:newBrand) ";
			SQLQuery query = session.createSQLQuery(sql);

			query.setParameter("mchtCd", snInfo.getMercCode());
			query.setParameter("termId", snInfo.getTermCode());
			
			query.setParameter("oriSn", snInfo.getOriSn());
			query.setParameter("newSn", snInfo.getNewSn());
			query.setParameter("newBrand", snInfo.getNewPosBrand());
			
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("添加原始序列号成功!!!");
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("添加原始序列号出错");
		}
	}
	

	@Override
	public int selectYsbD0FeeInfo(TPosInfo tPosInfo) throws DaoException {
		int icount = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql=" SELECT count(*) from apsmgm.tbl_mcht_biz_fee t where t.prod_cd = '1171' and t.mcht_cd = :mchtCd ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			Object obj=query.uniqueResult();
			if(obj!=null){
				icount = Integer.valueOf(obj.toString());// 云商宝D0费率数
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询D0费率出错");
		}
		return icount;
	}

	@Override
	public void dealYsbD0FeeInfo(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="delete FROM apsmgm.tbl_mcht_biz_fee t where t.prod_cd = '1171' and t.mcht_cd = :mchtCd ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("删除D0费率的数据成功!!!");
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("删除D0费率的数据出错");
		}
	
	}

	@Override
	public void dealYsbT1HisFeeInfo(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			
			String sql=" delete from tbl_mcht_biz_fee_his t where t.mcht_cd = :mchtCd and to_char(t.rec_crt_ts, 'YYYYMMDD') = to_char(sysdate, 'YYYYMMDD') ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("删除云商宝当天T1历史费率的数据成功!!!");
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("删除云商宝当天T1历史费率的数据出错");
		}
	
	}

	@Override
	public void updYsbT1FeeByFeeCd( String mchtFeePercent,String mchtFeePctMax, String mchtCd , String subBizCd ) throws DaoException{
		int i = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sb = new StringBuffer();
			sb.append(" update tbl_mcht_biz_fee t ");
			sb.append(" set t.mcht_fee_percent = :mchtFeePercent,t.mcht_fee_pct_max = :mchtFeePctMax ");
			sb.append(" where t.mcht_cd = :mchtCd ");
			sb.append(" and t.prod_cd = '1000' ");
   			sb.append(" and t.sub_biz_cd = :subBizCd ");
   			
			Query query = session.createSQLQuery(sb.toString());
			query.setParameter("mchtFeePercent", mchtFeePercent);
			query.setParameter("mchtFeePctMax", mchtFeePctMax);
			query.setParameter("mchtCd", mchtCd);
			query.setParameter("subBizCd", subBizCd);
			i = query.executeUpdate();	
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("修改云商宝T1费率出错");
		}
	}
	

	@Override
	public int selectYsbD0ProductInfo(TPosInfo tPosInfo) throws DaoException {
		int icount = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql=" SELECT count(*) FROM apsmgm.tbl_mcht_biz_deal t where t.prod_cd = '1171' and t.mcht_cd = :mchtCd ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			Object obj=query.uniqueResult();
			if(obj!=null){
				icount=Integer.valueOf(obj.toString());// 云商宝D0产品数
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询D0产品出错");
		}
		return icount;
	}

	@Override
	public void dealYsbD0ProductInfo(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="delete FROM apsmgm.tbl_mcht_biz_deal t where t.prod_cd = '1171' and t.mcht_cd = :mchtCd ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("删除D0产品的数据成功!!!");
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("删除D0产品的数据出错");
		}
	
	}

	@Override
	public int selectYsbHxD0ProductInfo(TPosInfo tPosInfo) throws DaoException {
		int icount = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql=" SELECT count(*) FROM apstfr.tbl_mcht_biz_deal t where t.prod_cd = '1171' and t.mcht_cd = :mchtCd ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			Object obj=query.uniqueResult();
			if(obj!=null){
				icount=Integer.valueOf(obj.toString());// 云商宝核心D0产品数
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询核心D0产品出错");
		}
		return icount;
	}
	
	@Override
	public void dealYsbHxD0ProductInfo(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="delete FROM apstfr.tbl_mcht_biz_deal t where t.prod_cd = '1171' and t.mcht_cd = :mchtCd ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("删除核心D0产品的数据成功!!!");
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("删除核心D0产品的数据出错");
		}
	
	}

	@Override
	public String queryRevSnByAgentId(String serialNum,String posBrand,Operators loginOper) throws DaoException {
		String result = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sb = new StringBuffer();
			sb.append(" select DISTINCT y.asset_flag from T_Pos_Info y,T_Pos_Agent x "
					+ " where y.serial_Num = x.serial_Num and y.pos_Brand = x.pos_Brand "
					+ " and x.is_Used = 'Y' " 
					+ " and y.serial_Num = '" + serialNum + "' and y.pos_Brand = '" + posBrand + "' ");
			// 如果当前登陆账号非系统管理员
			if ( !loginOper.getRoleType().equals( Role.SYSTEM.getCode() )  ){
				sb.append(" and x.agent_Id = " + loginOper.getId() + " ");

				// 判断是否 该导入人的上级，如果是，不允许撤回Sn  注：代理商自己导入的SN 上级不可以撤回，仓库分配的SN可以各个级别的撤回

				sb.append(" and  " + loginOper.getId() + " not in ");
				sb.append(" (SELECT t.id ");
				sb.append("    from apsmgm.t_operators t ");
				sb.append("   start with t.id = (select t.supri_id ");
				sb.append("                        from apsmgm.t_operators t ");
				sb.append("                       where t.account_no = ");
				sb.append("                             (select y1.operators ");
				sb.append("                                from apsmgm.T_Pos_Info y1 ");
				sb.append("                               where y1.serial_num = '" + serialNum + "' ");
				sb.append("                                 and y1.pos_Brand = '" + posBrand + "')) ");
				sb.append("  connect by nocycle prior t.supri_id = t.id) ");
				
			} else {
				// 导入代理商的关联信息是否已分配
				sb.append(" and y.operators = ( select t.account_no from t_operators t where t.id = x.agent_id )");
			}
			SQLQuery query = session.createSQLQuery(sb.toString());
			Object obj = query.uniqueResult();
			if ( null != obj ){
				result = obj.toString();
			}
		}catch(NonUniqueResultException nure){
			logger.debug("查询云商宝撤回SN不唯一", nure);
			throw new DaoException("查询云商宝撤回SN不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询云商宝撤回SN不唯一,查询出错");
		}
		return result;
	}

	@Override
	public void revYsbTermSNAgentInfo(String sn,String posBandCode,String asset_flag, Operators op)
			throws DaoException {
		int i = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sb = new StringBuffer();
			
		//	sb.append(" SELECT * ");
			sb.append(" DELETE ");
			sb.append("   FROM T_POS_AGENT T ");
			sb.append("  WHERE T.SERIAL_NUM = '" + sn + "' ");
			sb.append("    AND T.POS_BRAND = '" + posBandCode + "' ");
			// 如果当前登陆账号非系统管理员 ，删除 当前账号下的关联信息
			if ( !op.getRoleType().equals( Role.SYSTEM.getCode() ) && !op.getRoleType().equals( Role.WARE_HOUSE_LEAD.getCode() )  ){

				sb.append("    AND T.AGENT_ID in (SELECT T1.AGENT_ID AGENT_ID ");
				sb.append("              FROM (SELECT t.*, ");
				sb.append("                           (SELECT O.SUPRI_ID ");
				sb.append("                              FROM APSMGM.T_OPERATORS O ");
				sb.append("                             WHERE O.ID = T.AGENT_ID) SUP_ID ");
				sb.append("                      FROM APSMGM.t_Pos_Agent t ");
				sb.append("                     where t.serial_num = '" + sn + "' ");
				sb.append("                       and t.pos_brand = '" + posBandCode + "') T1 ");
				sb.append("             START WITH t1.AGENT_ID = '" + op.getId() + "' ");
				sb.append("            CONNECT BY PRIOR T1.AGENT_ID = T1.SUP_ID) ");
				sb.append("    AND T.AGENT_ID != '" + op.getId() + "' ");
				
			} else {
				// 如果删除的是 定制机 或 登陆账号为 仓库管理员
				if ( !asset_flag.equals("2") || op.getRoleType().equals( Role.WARE_HOUSE_LEAD.getCode() ) )  {
					// 否则，删除非导入账号信息的其他关联信息
					sb.append("    AND T.AGENT_ID != (SELECT (SELECT ID ");
					sb.append("                                 FROM T_OPERATORS O ");
					sb.append("                                WHERE O.ACCOUNT_NO = T2.OPERATORS) ID ");
					sb.append("                  FROM T_POS_INFO T2 ");
					sb.append("                 WHERE T2.SERIAL_NUM = '" + sn + "' ");
					sb.append("                   AND T2.POS_BRAND = '" + posBandCode + "' ) ");
				} else {  
					sb.append("    AND T.AGENT_ID in ");
					sb.append("    (SELECT T1.AGENT_ID AGENT_ID ");
					sb.append("       FROM (SELECT t.*, ");
					sb.append("                    (SELECT O.SUPRI_ID ");
					sb.append("                       FROM APSMGM.T_OPERATORS O ");
					sb.append("                      WHERE O.ID = T.AGENT_ID) SUP_ID ");
					sb.append("               FROM APSMGM.t_Pos_Agent t ");
					sb.append("              where t.serial_num = '" + sn + "' ");
					sb.append("                and t.pos_brand = '" + posBandCode + "') T1 ");
					sb.append("      START WITH t1.AGENT_ID = ");
					sb.append("                 (SELECT (SELECT O1.id ");
					sb.append("                            FROM APSMGM.T_OPERATORS O1 ");
					sb.append("                           WHERE O1.Account_No = IK.Operators) ");
					sb.append("                    FROM apsmgm.T_POS_INFO IK ");
					sb.append("                   WHERE IK.SERIAL_NUM = T.SERIAL_NUM ");
					sb.append("                     AND IK.POS_BRAND = T.POS_BRAND) ");
					sb.append("     CONNECT BY PRIOR T1.AGENT_ID = T1.SUP_ID) ");
					sb.append("  AND T.AGENT_ID != (SELECT (SELECT O2.id ");
					sb.append("                             FROM APSMGM.T_OPERATORS O2 ");
					sb.append("                            WHERE O2.Account_No = IZ.Operators) ");
					sb.append("                     FROM apsmgm.T_POS_INFO IZ ");
					sb.append("                    WHERE IZ.SERIAL_NUM = T.SERIAL_NUM ");
					sb.append("                      AND IZ.POS_BRAND = T.POS_BRAND) ");
					
				}
			}
/*
			sb.append("    AND EXISTS( SELECT 1 FROM T_POS_INFO I1 ");
			sb.append("                 WHERE I1.SERIAL_NUM = T.SERIAL_NUM ");
			sb.append("                   AND I1.POS_BRAND = T.POS_BRAND  ");
			sb.append("                   AND I1.IS_BIND = 'N' ) ");*/

			Query query = session.createSQLQuery(sb.toString());
			i = query.executeUpdate();	
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("撤回云商宝sn关联信息出错");
		}
	}
	@Override
	public void updYsbTermSNAgentUsedStatus(String sn,String posBandCode, Operators op)
			throws DaoException {
		int i = 0;
		try {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sb = new StringBuffer();
			
			sb.append(" UPDATE T_POS_AGENT Y ");
			sb.append("    SET Y.IS_USED = 'N', ");
			sb.append("            Y.DT_LASTMOD  = SYSTIMESTAMP, ");
			sb.append("            Y.ZS_FLAG  = 'Y' ");

			// 如果当前登陆账号非系统管理员 ，修改 备注信息为 当前登陆帐号上级 的备注信息
			if ( !op.getRoleType().equals( Role.SYSTEM.getCode() )  && !op.getRoleType().equals( Role.WARE_HOUSE_LEAD.getCode() )  ){
			/*	sb.append("        Y.REMARK  = (SELECT REMARK ");
				sb.append("                       FROM (SELECT T.REMARK ");
				sb.append("                               FROM T_POS_INFO T ");
				sb.append("                              WHERE T.SERIAL_NUM = '" + sn + "' ");
				sb.append("                                AND T.POS_BRAND = '" + posBandCode + "' ");
				sb.append("                                AND OPERATORS = '" + op.getAccountNo() + "') ");
				sb.append("                     UNION ALL (SELECT DISTINCT T.REMARK ");
				sb.append("                                 FROM T_POS_AGENT T ");
				sb.append("                                WHERE T.SERIAL_NUM = '" + sn + "' ");
				sb.append("                                  AND T.POS_BRAND = '" + posBandCode + "' ");
				sb.append("                                  AND ( T.REMARK LIKE '%分配给[" + op.getAccountNo() + "]'  or T.AGENT_ID = '" + op.getSuperiorId() + "') ) ");
				sb.append("                     ) ");*/
			} else {
				// 如果是系统管理员或仓库管理员，终端等级 置为空
				sb.append("            ,Y.FLAG  = '' ");/*
				// 否则，修改 备注信息为 导入账号 的备注信息
				sb.append("        Y.REMARK  = ( SELECT T.REMARK ");
				sb.append("                               FROM T_POS_INFO T ");
				sb.append("                              WHERE T.SERIAL_NUM = '" + sn + "' ");
				sb.append("                                AND T.POS_BRAND = '" + posBandCode + "' ");
				sb.append("                     ) ");*/
			}
			sb.append("  WHERE Y.SERIAL_NUM = '" + sn + "' ");
			sb.append("    AND Y.POS_BRAND = '" + posBandCode + "' ");
/*
			sb.append("    AND EXISTS( SELECT 1 FROM T_POS_INFO I1 ");
			sb.append("                 WHERE I1.SERIAL_NUM = Y.SERIAL_NUM ");
			sb.append("                   AND I1.POS_BRAND = Y.POS_BRAND  ");
			sb.append("                   AND I1.IS_BIND = 'N' ) ");*/
			
			if ( !op.getRoleType().equals( Role.SYSTEM.getCode() )  ){
				sb.append("    AND Y.AGENT_ID = '" + op.getId() + "' ");
			} else {
				sb.append("    AND  ( select t.account_no from t_operators t where t.id = Y.agent_id ) = ");
				sb.append("    		( SELECT T.operators ");
				sb.append("                  FROM T_POS_INFO T ");
				sb.append("                  WHERE T.SERIAL_NUM = '" + sn + "' ");
				sb.append("                        AND T.POS_BRAND = '" + posBandCode + "' ");
				sb.append("          ) ");
			}
			
			Query query = session.createSQLQuery(sb.toString());
			i = query.executeUpdate();	
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("修改云商宝sn关联信息 使用状态 出错");
		}
	}

	@Override
	public String selectYsbDirAgentByTerm(TPosInfo tPosInfo) throws DaoException {
		String agentId="";
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql=" select p.agent_id "
					+ "  from t_pos_INFO t, t_pos_agent p "
					+ " where t.serial_num = p.serial_num "
					+ "   and t.pos_brand = p.pos_brand "
					+ "   and t.merc_code = :mid"
					+ "   and t.term_code = :tid "
					+ "   and p.zs_flag = 'Y' ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("mid", tPosInfo.getMercCode());
			query.setParameter("tid", tPosInfo.getTermCode());
			Object obj=query.uniqueResult();
			if(obj!=null){
				agentId=String.valueOf(obj);// SN关系表直属代理商id
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询SN关系表直属代理商id出错");
		}
		return agentId;
	}

	@Override
	public TPosInfo selectYsbSnInfoByAgent(TPosInfo tPosInfo) throws DaoException {
 		TPosInfo bean = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql=" select t.is_bind,p.is_used "
					+ "  from t_pos_INFO t, t_pos_agent p "
					+ " where t.serial_num = p.serial_num "
					+ "   and t.pos_brand = p.pos_brand "
					+ "   and t.serial_num = :serialNum"
					+ "   and t.pos_brand = :posBrand "
					+ "   and p.agent_id = :agentId "
					;
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("serialNum", tPosInfo.getSerialNum());
			query.setParameter("posBrand", tPosInfo.getPosBrand());
			query.setParameter("agentId", tPosInfo.getAgentId());

		 	Iterator iterator= query.list().iterator();
		 	while(iterator.hasNext()){
		 		Object[] obj = (Object[]) iterator.next();
		 		bean = new TPosInfo();
		 		bean.setIsBind(obj[0].toString());
		 		bean.setIsUsed(obj[1].toString());
		 	}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询云商宝sn绑定信息出错");
		}
		return bean;
	}

	@Override
	public void boundYsbPosAgent(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="update t_pos_agent set is_used=:used,dt_lastmod=systimestamp where serial_num=:serialNum AND Pos_Brand=:brand and ZS_FLAG=:zsFlag ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("used", tPosInfo.getIsUsed());
			query.setParameter("serialNum", tPosInfo.getSerialNum());
			query.setParameter("brand", tPosInfo.getPosBrand());
			query.setParameter("zsFlag", "Y");
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("修改云商宝SN关系信息成功!!!");
			}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("修改云商宝SN关系信息出错");
			}
	}

	@Override
	public void boundYsbPosInfo(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql="update t_pos_info s set s.is_bind=:isBind,s.dt_lastmod=systimestamp,s.merc_code=:mid,s.term_code=:tid where s.serial_num=:serialNum and s.pos_brand=:brand ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("isBind", tPosInfo.getIsBind());
			query.setParameter("mid", tPosInfo.getMercCode());
			query.setParameter("tid", tPosInfo.getTermCode());
			query.setParameter("serialNum", tPosInfo.getSerialNum());
			query.setParameter("brand", tPosInfo.getPosBrand());
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("修改云商宝SN信息成功!!!");
			}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("修改云商宝SN信息出错");
			}
	}


	@Override
	public void replaceTerminal(TPosInfo tPosInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql = "update tbl_term_inf t set t.device_cd=:serialNum, "
						+ " t.tmnl_brand=:brand,t.tmnl_model_no=:model,t.rec_upd_ts=systimestamp, t.rec_opr_id='U'  "
						+ "where t.mcht_cd=:mchtCd and t.term_id=:termId ";
			SQLQuery query = session.createSQLQuery(sql);

			query.setParameter("serialNum", tPosInfo.getSerialNum());
			query.setParameter("brand", tPosInfo.getPosBrand());
			query.setParameter("model", tPosInfo.getPosName());
			
			query.setParameter("mchtCd", tPosInfo.getMercCode());
			query.setParameter("termId", tPosInfo.getTermCode());
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("云商宝终端换机成功!!!");
			}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("云商宝终端换机出错");
			}
	}
	
	@Override
	public void updateSnInfo(YjSnInfo yjSnInfo) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			
			String sql = " update yj_sn_info s set  "
						+ " s.NEW_SN= :newSn,s.NEW_POS_BRAND = :newPosBrand " 
						+ "where s.merc_code=:mchtCd and s.term_code=:termId ";
			SQLQuery query = session.createSQLQuery(sql);

			query.setParameter("newSn", yjSnInfo.getNewSn());
			query.setParameter("newPosBrand", yjSnInfo.getNewPosBrand());

			query.setParameter("mchtCd", yjSnInfo.getMercCode());
			query.setParameter("termId", yjSnInfo.getTermCode());
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("修改原始序列号成功!!!");
			}
			}catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new DaoException("修改原始序列号出错");
			}
	}
	@Override
	public void insertTPosReplace(TPosReplace posReplace) throws DaoException {
		int icount=0;
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql = " insert into t_pos_replace(MER_NO , OLDSERIAL_NUM , NEWSERIAL_NUM , OPERATE_DATE , OLDBRANDNO , OLDMODELNO , NEWBRANDNO , NEWMODELNO ,OPER_ID) "
					+ " values(:merNo , :oldserialNum , :newserialNum , :operateDate , :oldbrandno , :oldmodelno , :newbrandno , :newmodelno ,:operId) ";
			SQLQuery query = session.createSQLQuery(sql);

			query.setParameter("merNo", posReplace.getMerNo());
			query.setParameter("oldserialNum", posReplace.getOldserialNum());
			query.setParameter("newserialNum", posReplace.getNewserialNum());
			query.setParameter("operateDate", posReplace.getOperateDate());
			query.setParameter("oldbrandno", posReplace.getOldbrandNo());
			query.setParameter("oldmodelno", posReplace.getOldmodelNo());
			query.setParameter("newbrandno", posReplace.getNewbrandNo());
			query.setParameter("newmodelno", posReplace.getNewmodelNo());
			query.setParameter("operId", posReplace.getOperId());
			icount=query.executeUpdate();
			if(icount>0) {
				logger.info("添加云商宝Sn换机记录成功!!!");
			}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("添加云商宝Sn换机记录出错");
		}
	}


	@Override
	public Map<String, String> selectAllSuperInfoByAgentId(Operators op) throws DaoException {
 		Map<String, String> retMap = new HashMap<String, String>();
		try {
			Session session = sessionFactory.getCurrentSession();
			String sql=   " SELECT t.id,t.account_no " 
						+ "   from apsmgm.t_operators t " 
						+ "  start with t.id = :opId " 
						+ " connect by nocycle prior t.supri_id = t.id " 
						+ "  order by t.dt_create "
					;
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("opId", op.getId());

		 	Iterator iterator= query.list().iterator();
		 	while(iterator.hasNext()){
		 		Object[] obj = (Object[]) iterator.next();
		 		retMap.put(obj[0].toString(), obj[1].toString());
		 	}
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询所有上级信息出错");
		}
		return retMap;
	}
			
}
 